package cn.cloudtogo.server.mysql.service;

import cn.cloudtogo.common.Assert;
import cn.cloudtogo.common.GendbException;
import cn.cloudtogo.common.model.ExternalDatasourceDao;
import cn.cloudtogo.common.model.ExternalDatasourceModel;
import cn.cloudtogo.common.types.Pair;
import cn.cloudtogo.common.types.RequestHeaderObject;
import cn.cloudtogo.server.mysql.SpringJdbcTemplateContext;
import cn.cloudtogo.support.mysql.Condition;
import cn.cloudtogo.support.mysql.column.ColumnApi;
import cn.cloudtogo.support.mysql.column.ColumnDefinition;
import cn.cloudtogo.support.mysql.table.TableApi;
import cn.cloudtogo.support.mysql.table.TableMetadata;
import cn.cloudtogo.support.mysql.table.TableRowApi;
import lombok.extern.slf4j.Slf4j;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.stereotype.Component;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.support.DefaultTransactionDefinition;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import java.time.LocalDateTime;
import java.util.*;
import java.util.stream.Collectors;

import static cn.cloudtogo.common.Assert.argument;
import static cn.cloudtogo.common.Assert.notNull;
import static cn.cloudtogo.common.types.RequestHeaderObject.extractExternalDatasourceModel;
import static cn.cloudtogo.common.util.StringHelper.pretty;

/**
 * @author yaolianhua789@gmail.com
 **/
@Component
@Slf4j
public class TableRows implements TableRowApi {

    private final SpringJdbcTemplateContext context;
    private final TableApi tableApi;
    private final ColumnApi columnApi;
    private final ExternalDatasourceDao externalDatasourceDao;

    public TableRows(SpringJdbcTemplateContext context,
                     TableApi tableApi,
                     ColumnApi columnApi,
                     ExternalDatasourceDao externalDatasourceDao) {
        this.context = context;
        this.tableApi = tableApi;
        this.columnApi = columnApi;
        this.externalDatasourceDao = externalDatasourceDao;
    }

    private RequestHeaderObject obtainRequestHeaderObject(String uuid) {
        ExternalDatasourceModel externalDatasourceModel = externalDatasourceDao.query(uuid);
        Assert.notNull(externalDatasourceModel, () -> "invalid uuid [" + uuid + "]");
        return RequestHeaderObject.ofModel(externalDatasourceModel);
    }

    private void fieldCheck(RequestHeaderObject requestHeaderObject, String table, Pair pair) {
        List<String> tables = tableApi.tables(requestHeaderObject);
        if (!tables.contains(table)) {
            throw new GendbException(String.format("table '%s' does not exist", table));
        }

        TableMetadata tableMetadata = tableApi.tableMetadata(requestHeaderObject, table);
        List<String> columns = tableMetadata.getColumns()
                .stream()
                .map(ColumnDefinition::getName)
                .collect(Collectors.toList());

        Set<String> fields = pair.keySet();
        if (CollectionUtils.isEmpty(fields)) {
            throw new GendbException("field is empty");
        }
        for (String field : fields) {
            if (!columns.contains(field)) {
                throw new GendbException(String.format("field '%s' does not exist", field));
            }
        }

    }

    @Override
    public int insert(String uuid, String table, Pair pair) {
        notNull(pair, () -> "insert data is null");
        RequestHeaderObject requestHeaderObject = obtainRequestHeaderObject(uuid);
        fieldCheck(requestHeaderObject, table, pair);

        JdbcTemplate jdbcTemplate = context.getJdbcTemplate(requestHeaderObject);
        SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(jdbcTemplate);

        populateDefaultValue(requestHeaderObject, table, pair);

        ExternalDatasourceModel model = extractExternalDatasourceModel(requestHeaderObject);
        int execute = simpleJdbcInsert
                .withTableName(String.format("`%s`.`%s`", model.getSchema(), table))
                .execute(pair);
        log.debug(pretty(requestHeaderObject, "Insert data", table, pair));
        return execute;

    }

    @Transactional(rollbackFor = Exception.class)
    @Override
    public List<Pair> insertBatch(String uuid, String table, Pair... pairs) {
        notNull(pairs, () -> "insert data is null");
        argument(pairs.length > 0, () -> "insert data is empty");

        RequestHeaderObject requestHeaderObject = obtainRequestHeaderObject(uuid);
        Arrays.stream(pairs).forEach(e -> fieldCheck(requestHeaderObject, table, e));

        populateDefaultValue(requestHeaderObject, table, pairs);

        log.debug(pretty(requestHeaderObject, "Insert batch data", table, pairs));
        JdbcTemplate jdbcTemplate = context.getJdbcTemplate(requestHeaderObject);


        DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager(Objects.requireNonNull(jdbcTemplate.getDataSource()));
        DefaultTransactionDefinition defaultTransactionDefinition = new DefaultTransactionDefinition();
        TransactionStatus transactionStatus = dataSourceTransactionManager.getTransaction(defaultTransactionDefinition);

        ExternalDatasourceModel model = extractExternalDatasourceModel(requestHeaderObject);

        List<Pair> response = new LinkedList<>();
        try {
            for (Pair pair : pairs) {
                //fix: Configuration cannot be altered once the class has been compiled or used
                SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(jdbcTemplate);
                simpleJdbcInsert.setGeneratedKeyNames("id");
                Number number = simpleJdbcInsert
                        .withTableName(String.format("`%s`.`%s`", model.getSchema(), table))
                        .executeAndReturnKeyHolder(pair)
                        .getKey();
                //query id
                String key;
                if (null == number) {
                    //No auto-increment ID specified
                    List<String> primaries = columnApi.listPrimary(requestHeaderObject, table);
                    key = primaries.stream()
                            .map(pair::get)
                            .map(Object::toString)
                            .collect(Collectors.joining(","));

                } else {
                    key = String.valueOf(number.intValue());
                }
                response.add(find(uuid, table, key));
            }
            dataSourceTransactionManager.commit(transactionStatus);
            return response;
        } catch (Exception e) {
            dataSourceTransactionManager.rollback(transactionStatus);
            throw new GendbException(e.getMessage());
        }
    }

    @Override
    public int delete(String uuid, String table, String id) {
        RequestHeaderObject requestHeaderObject = obtainRequestHeaderObject(uuid);
        JdbcTemplate jdbcTemplate = context.getJdbcTemplate(requestHeaderObject);

        ExternalDatasourceModel model = extractExternalDatasourceModel(requestHeaderObject);
        String primaryKeyCondition = primaryKeyCondition(requestHeaderObject, table, id);

        String sql = String.format("delete from `%s`.`%s` where %s", model.getSchema(), table, primaryKeyCondition);
        log.debug(pretty(requestHeaderObject, "Delete data", table, sql));
        return jdbcTemplate.update(sql);
    }

    @Transactional(rollbackFor = Exception.class)
    @Override
    public int deleteBatch(String uuid, String table, String primaryKey, String idComma) {
        Assert.state(StringUtils.hasText(idComma), () -> "idComma parameter is null", 400);
        RequestHeaderObject requestHeaderObject = obtainRequestHeaderObject(uuid);
        JdbcTemplate jdbcTemplate = context.getJdbcTemplate(requestHeaderObject);

        ExternalDatasourceModel model = extractExternalDatasourceModel(requestHeaderObject);

        List<String> primaries = columnApi.listPrimary(requestHeaderObject, table);
        if (StringUtils.hasText(primaryKey)) {
            Assert.state(primaries.size() == 1, () -> "No unique primary key found");
            Assert.state(primaries.contains(primaryKey), () -> "Named primary key is wrong", 400);
        }

        String sql = String.format("delete from `%s`.`%s` where `%s` in (%s)", model.getSchema(), table, primaries.get(0), idComma);
        log.debug(pretty(requestHeaderObject, "Delete data", table, sql));
        return jdbcTemplate.update(sql);

    }

    @Override
    public Pair update(String uuid, String table, String id, Pair pair) {

        List<String> columns = new LinkedList<>();
        List<Object> args = new LinkedList<>();

        pair.forEach((column, value) -> {
            columns.add(String.format("`%s`=?", column));
            args.add(value);
        });
        String commaColumn = String.join(",", columns);

        Object[] params = args.toArray(new Object[]{});
        RequestHeaderObject requestHeaderObject = obtainRequestHeaderObject(uuid);
        JdbcTemplate jdbcTemplate = context.getJdbcTemplate(requestHeaderObject);

        ExternalDatasourceModel model = extractExternalDatasourceModel(requestHeaderObject);
        String primaryKeyCondition = primaryKeyCondition(requestHeaderObject, table, id);
        String sql = String.format("update `%s`.`%s` set %s where %s", model.getSchema(), table, commaColumn, primaryKeyCondition);
        log.debug(pretty(requestHeaderObject, "Update data", table, sql));

        jdbcTemplate.update(sql, params);
        return find(uuid, table, id);
    }

    @Override
    public Pair find(String uuid, String table, String id) {
        RequestHeaderObject requestHeaderObject = obtainRequestHeaderObject(uuid);
        JdbcTemplate jdbcTemplate = context.getJdbcTemplate(requestHeaderObject);

        ExternalDatasourceModel model = extractExternalDatasourceModel(requestHeaderObject);
        String primaryKeyCondition = primaryKeyCondition(requestHeaderObject, table, id);
        String sql = String.format("select * from `%s`.`%s` where %s", model.getSchema(), table, primaryKeyCondition);
        log.debug(pretty(requestHeaderObject, "Query data", table, sql));
        try {
            Map<String, Object> one = jdbcTemplate.queryForMap(sql);
            return Pair.pair(one);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }

    }

    private List<ColumnDefinition> obtainColumnsOfCurrentTimestampTypes(RequestHeaderObject object, String table) {
        TableMetadata metadata = tableApi.tableMetadata(object, table);
        return metadata.getColumns()
                .stream()
                .filter(e -> "DATETIME".equalsIgnoreCase(e.getTypes().getColumnType().name()) ||
                        "TIMESTAMP".equalsIgnoreCase(e.getTypes().getColumnType().name()))
                .filter(e -> "CURRENT_TIMESTAMP".equalsIgnoreCase(e.getDefaultValue()))
                .collect(Collectors.toList());
    }

    private void populateDefaultValue(RequestHeaderObject object, String table, Pair... pairs) {
        //fix CURRENT_TIMESTAMP默认值不生效，没有的列spring会进行填充null
        List<ColumnDefinition> columns = obtainColumnsOfCurrentTimestampTypes(object, table);
        for (Pair pair : pairs) {
            for (ColumnDefinition definition : columns) {
                Object o = pair.getOrDefault(definition.getName(), LocalDateTime.now());
                pair.put(definition.getName(), o);
            }
        }

    }

    private String primaryKeyCondition(RequestHeaderObject requestHeaderObject, String table, String id) {

        String[] ids = StringUtils.commaDelimitedListToStringArray(id);

        TableMetadata tableMetadata = tableApi.tableMetadata(requestHeaderObject, table);
        List<ColumnDefinition> primaryColumnMetadataList = tableMetadata.getColumns()
                .stream()
                .filter(ColumnDefinition::isPrimaryKey)
                .collect(Collectors.toList());
        Assert.state(ids.length == primaryColumnMetadataList.size(), () -> "id parameters not matched, primary key size[" + primaryColumnMetadataList.size() + "]");

        StringBuilder idBuilder = new StringBuilder();
        String prefix = "1=1";
        for (int i = 0; i < primaryColumnMetadataList.size(); i++) {
            if ("VARCHAR".equalsIgnoreCase(primaryColumnMetadataList.get(i).getTypes().getColumnType().name())) {
                idBuilder.append(primaryColumnMetadataList.get(i).getName()).append(" = ").append("'").append(ids[i]).append("'");
            } else {
                idBuilder.append(primaryColumnMetadataList.get(i).getName()).append(" = ").append(ids[i]);
            }
            idBuilder.append(" and ");
        }
        return idBuilder.append(prefix).toString();
    }

    @Override
    public List<Pair> find(String uuid, String table, Condition condition) {
        condition = Objects.isNull(condition) ? new Condition() : condition;
        RequestHeaderObject requestHeaderObject = obtainRequestHeaderObject(uuid);
        JdbcTemplate jdbcTemplate = context.getJdbcTemplate(requestHeaderObject);
        String where = "1=1";
        if (StringUtils.hasText(condition.getWhere())) {
            where = condition.getWhere();
        }
        int page = Math.max(condition.getPage(), 1);
        int size = condition.getSize() < 1 ? 100 : condition.getSize();
        size = Math.min(size, 5000);

        int offset = (page - 1) * size;
        String limit = String.format("limit %s,%s", offset, size);

        String column = "*";

        if (StringUtils.hasText(condition.getFields())) {
            column = condition.getFields();
        }

        ExternalDatasourceModel model = extractExternalDatasourceModel(requestHeaderObject);
        String sql = String.format("select %s from `%s`.`%s` where %s %s", column, model.getSchema(), table, where, limit);
        log.debug(pretty(requestHeaderObject, "Query data", table, sql));
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);

        List<Pair> pairs = maps.stream().map(Pair::pair).collect(Collectors.toList());
        return pairs;
    }

    @Override
    public int count(String uuid, String table, String whereCondition) {
        RequestHeaderObject requestHeaderObject = obtainRequestHeaderObject(uuid);
        JdbcTemplate jdbcTemplate = context.getJdbcTemplate(requestHeaderObject);
        String where = "1=1";
        if (StringUtils.hasText(whereCondition)) {
            where = whereCondition;
        }

        ExternalDatasourceModel model = extractExternalDatasourceModel(requestHeaderObject);
        String sql = String.format("select count(*) from `%s`.`%s` where %s", model.getSchema(), table, where);
        log.debug(pretty(requestHeaderObject, "Query data", table, sql));
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
        return count == null ? 0 : count;
    }

}
